Opening a Excel Spreadsheet and accessing a CELL in DXL

Dear All,

I want to in DXL:
1. Open an Excel spreadsheet.
2. Access a particular cell within the spreadsheet.
3. Assign the value of the cell to a DOORS attribute.

Any help would be much appreciated.
MichaelNugent - Fri Nov 12 05:50:41 EST 2010

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
Mathias Mamsch - Sun Nov 14 14:01:03 EST 2010

There is an example in the DOORS DXL help ("Automation client support") which will do the opposite, i.e. write a value to an excel sheet, but that is pretty much the same. Any luck trying the example?

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
SystemAdmin - Thu Nov 18 07:22:33 EST 2010

Michael,

Have a look at the attached file. Change the last bit to set the attribute.
Attachments

attachment_14554197_ExcelGetCellValues.dxl

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
SpaceApe - Sun Dec 18 10:42:56 EST 2011

SystemAdmin - Thu Nov 18 07:22:33 EST 2010
Michael,

Have a look at the attached file. Change the last bit to set the attribute.

With following configuration, integer in excel cell is returned as null by the DXL script provided by NilsPalsson about an year back (in 2010).

1) Office 10
2) DOORS 9.3

Any idea what might be the cause? and work-around it?

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
SystemAdmin - Mon Dec 19 04:00:24 EST 2011

SpaceApe - Sun Dec 18 10:42:56 EST 2011
With following configuration, integer in excel cell is returned as null by the DXL script provided by NilsPalsson about an year back (in 2010).

1) Office 10
2) DOORS 9.3

Any idea what might be the cause? and work-around it?

Tried the DXL script with Excel 2010. It did work in the situation you described when I used

int iValue = 0

in getCellValue instead of the original string variable.

Pekka Mäkinen - http://www.softqa.eu/

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
SystemAdmin - Thu Feb 09 09:00:26 EST 2012

SystemAdmin - Mon Dec 19 04:00:24 EST 2011

Tried the DXL script with Excel 2010. It did work in the situation you described when I used

int iValue = 0

in getCellValue instead of the original string variable.

Pekka Mäkinen - http://www.softqa.eu/

Pekka,
Thanks. It works with integer type variable. I didn't expect excel variable types to match to DXL variable types.

Thanks again

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
Avinash Singh - Thu Oct 24 02:56:43 EDT 2013

Mathias Mamsch - Sun Nov 14 14:01:03 EST 2010
There is an example in the DOORS DXL help ("Automation client support") which will do the opposite, i.e. write a value to an excel sheet, but that is pretty much the same. Any luck trying the example?

Regards, Mathias

Mathias Mamsch, IT-QBase GmbH, Consultant for Requirement Engineering and D00RS

hi Mathias Mamsch 2700025PVX,

new to this forum i need to import values from excel to doors attributes. I can get value from single cell to attribute in doors but problem i am facing is when the cells are MERGED in excel my script stops. if u have any idea on this please let me know. below is where i am getting the cell value from excel code section

string getCellValue(OleAutoObj objExcelSheet, int row, int col)
{
string sValue = null
OleAutoObj objCell = null
OleAutoArgs autoArgs = create
OleAutoObj objShapes

put(autoArgs,row)                                 // put the row number of excel sheet to args
put(autoArgs,col)          // put the col number of excel sheet to args
if(objExcelSheet == null)                         // check for excel sheet is null " no contents in the sheet "
{
  warningBox("Excel sheet not contains any data it is blank ")
}
oleGet(objExcelSheet,"Cells",autoArgs,objCell)        // if data is present then

if (!null objCell)
{
  // Get the value
  oleGet(objCell,"Value",sValue)                   // fetch the data from cell


}
delete autoArgs
return sValue                                        // return the fetched data of the cell with specific row and column
}

Thanks in advance

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
llandale - Thu Oct 24 11:04:21 EDT 2013

Avinash Singh - Thu Oct 24 02:56:43 EDT 2013

hi Mathias Mamsch 2700025PVX,

string getCellValue(OleAutoObj objExcelSheet, int row, int col)
{
string sValue = null
OleAutoObj objCell = null
OleAutoArgs autoArgs = create
OleAutoObj objShapes

Thanks in advance

Did some investigating. Open this file for an example (or whereever it is on your computer)

c:\Program files\IBM\Rational\DOORS\9.3\lib\dxl\standard\export\office\excel.dxl

Function "setCell()" line #172 suggests if you convert cell "2,4" to "B4" you can use the "Range" property to get your "Cells" range. It uses function "intToCol()" starting line #140 to convert "2" to "B".

In any event, if you have to arguments to a property (such as "Cells") you need to give them names. I could not find the names, but perhaps they are "rowNumber" and "colNumber".

put(autoArgs, "rowNumber", row)
put(autoArgs, "calNumber", col)

-Louie

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
pommCannelle - Fri Oct 25 08:36:24 EDT 2013

( Hi !

For your next try ... There is a very complete example to deal with the excel automation here : http://www.galactic-solutions.com/downloadsv/GalacticDownloadExcel.htm

... I hope there is no offense to provide an external link ! )

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
aeggarcia - Thu Jun 16 11:25:42 EDT 2016

SystemAdmin - Thu Feb 09 09:00:26 EST 2012
Pekka,
Thanks. It works with integer type variable. I didn't expect excel variable types to match to DXL variable types.

Thanks again

I have been trying migrating the string variable into a int variable as described.

In the end I get something display but is always "0". I am using DOORS 9.6 and office 10.

I tried to format the cells to txt, to General or numeric. To be honest it would help a lot to make it work with text.

Wondering if somebody can help.

Antonio

Re: Opening a Excel Spreadsheet and accessing a CELL in DXL
O.Wilkop - Fri Jun 17 06:51:06 EDT 2016

aeggarcia - Thu Jun 16 11:25:42 EDT 2016

I have been trying migrating the string variable into a int variable as described.

In the end I get something display but is always "0". I am using DOORS 9.6 and office 10.

I tried to format the cells to txt, to General or numeric. To be honest it would help a lot to make it work with text.

Wondering if somebody can help.

Antonio

Try using

oleGet(objCell, "Text", sValue)

instead of

oleGet(objCell, "Value", sValue)

That works for me with both, number or text values in the excel cell.